Full schema preview

本日所有schema搶先看。

劇情提要

毒品交易失敗後，韓琛確信身邊有警察臥底。依照建明要求，將所有手下的個人資料裝在信封中，於電影院L13位置交給建明。永仁偷偷在後觀察並尾隨建明離開，欲看清其模樣，不料手機突然響起，因而錯失良機。而建明也隱約感覺到有人跟蹤，隱於牆後查看但未發現人跡。另一方面，警隊高層也懷疑韓琛安插了臥底，於是將建明調至內務部並在O記辦公，專門調查此事。建明依照韓琛所給資料於警隊資料庫中進行搜尋，卻無發現。於此同時，韓琛試探了身邊幾個親近手下，包括永仁與傻強。

此劇照引用自IMDb-無間道

EdgeQL query

建立Envelope

由於信封在本劇後半段是一個重要的物件，除了裡面有參與韓琛毒品交易手下的資料外，更特別的是信封上有一個永仁親手寫的「標」字。於是我們想為信封建立一個object type，但同時希望這麼特別的信封只能被生成一次，也就是只能insert一個Envelope object。

我們準備借助EdgeDB的access policy來完成這個需求，其有特殊的resolution order：

• 當object type上沒有施加任何access policy時，這個object type可以被讀取及變動。

• 當object type上有施加任何access policy時，會拆成三個步驟來決定允許操作的範圍：

  • 首先，所有操作先變為deny。
  • 接著，允許標示有allow的操作。
  • 最後，排除標示有deny的操作。

依照上述原理，我們可以建立Envelope的schema，其有一個property及兩個access policy：

• name property為永仁所寫的錯別字「標」，並設定readonly為true。代表當給予預設值後，無法變更此property。
• 第一個policy命名為allow_select_insert_delete，允許進行select、 insert及delete。
• 第二個policy命名為only_one_envelope_exists，使用using (exists Envelope)作為判斷條件，當資料庫中已經有存在Envelope object時，拒絕insert，並給定客製化的報錯訊息Only one Envelope can be existed.。

type Envelope {
    name: str {
        default:= "標";
        readonly:= true
    };
    access policy allow_select_insert_delete
        allow select, insert, delete;

    access policy only_one_envelope_exists
        deny insert
        using (exists Envelope)
        {
            errmessage := 'Only one Envelope can be existed.'
        };
}

readonly vs access policy

readonly是用在property上的constraint，access policy是適用在整個object type上。不過在我們這個例子中，因為沒有allow update，所以Envelope是不能update的，因此如果將readonly刪除也可以。此外，如果在這個例子中執行updatequery的話，EdgeDB並不會報錯，只會返回一個空set。

access policy的延伸應用

既然access policy可以讓我們限制insert的次數，這麼一來我們也可以延伸應用到對照警察職級表，來限制各職級的人數，例如只能insert一名處長（CP）級長官。

執行1st migration

did you create object type 'default::Envelope'? [y,n,l,c,b,s,q,?]
> y

insert Envelope

執行下面query可以成功insert一個Envelope object。

insert Envelope;

但是如果想再insert一個Envelope object的話，則會報錯如下：

edgedb error: AccessPolicyError: access policy violation on insert of default::Envelope (Only one Envelope can be existed.)

我們的客製化錯誤訊息成功被印出。

update lau

建明受上級指示調至內務部並在O記辦公，調查韓琛臥底。

update lau 
set {
    dept:= "投訴及內部調查科", 
};

使用ext::pg_trgm

建明拿到信封後，想利用警隊的資料庫系統查詢，參與毒品交易手下們的名字有沒有在其中。此時他可以利用EdgeDB的ext::pg_trgmextension來查詢。

using extension pg_trgm;

module default {
    ...
}

留意using extension module;的位置，不可以置於任何module內。

執行end migration

did you create extension 'pg_trgm'? [y,n,l,c,b,s,q,?]
> y

學習使用ext::pg_trgm

如果需要的是full text search，可以試試內建的fts模組。但是我自己在使用fts::index後做migration，常常會失敗。可能是我還沒掌握到正確使用方式或是版本功能尚未穩定。

word_similar()

首先建明使用ext::pg_trgm::word_similar()來進行查詢。這個function會計算第一個參數與第二個參數的任意部份相似的程度，並依據最高的分數是否超過預先設定的門檻值，來回傳bool值。

with names:= array_join(array_agg(Police.name), " "), 
       module ext::pg_trgm,
select word_similar("陳永仁", names);

{false}

這段query看起來有點複雜，我們逐個拆解：

• 在with區塊中，利用array_agg將Police.name這個set變為array。接著利用array_join將array中每一個element（str型態）用" "連接起來，命名為names。
• 在with區塊中，將預設module由default轉為ext::pg_trgm。
• 因為轉變了預設module，所以可以直接使用word_similar()查詢。

由於陳永仁這個名字的確沒有出現在Police.name中（陳永仁是PoliceSpy），所以建明得到false。

word_similarity()

如果建明不死心，想知道最高的分數實際上是多少的話，可以使用ext::pg_trgm::word_similarity()。

with names:= array_join(array_agg(Police.name), " "), 
       module ext::pg_trgm,
select word_similarity("陳永仁", names);

{0}

最終建明得到了最低分的0分，這下他徹底死心了。

平時時空的建明

word_similar()

假設平時時空的建明，得知警隊除了平常可以接觸的資料庫外，還有一個機密資料庫，所有臥底檔案都在其中，而他已設法取得權限。此時他將可以進行下列query：

with names:= array_join(array_agg(Police.name union PoliceSpy.name), " "), 
     module ext::pg_trgm,
select word_similar("陳永仁", names);

{true}

這一次平時時空的建明得到true，成功找出永仁。

word_similarity()

如果建明使用word_similarity，其query會像是：

with names:= array_join(array_agg(Police.name union PoliceSpy.name), " "), 
     module ext::pg_trgm,
select word_similarity("陳永仁", names);

{1}

建明會得到1分的最高分，一樣成功找出永仁。

此外，我們假設建明將陳永仁誤植為陳永仨，其query會像是：

with names:= array_join(array_agg(Police.name union PoliceSpy.name), " "), 
     module ext::pg_trgm,
select word_similarity("陳永仨", names);

{0.5}

這一次建明得到不低的0.5分，他將會以陳永仨為線索之一，繼續追查下去。

with區塊注意事項

with module相當於轉換預設module

下面這段query是錯誤的。

# ❌
with module ext::pg_trgm,
       names:= array_join(array_agg(Police.name), " "), 
select word_similar("陳永仁", names);

其報錯訊息為：

error: InvalidReferenceError: object type or alias 'ext::pg_trgm::Police' does not exist

因為此時module已經由default轉為ext::pg_trgm，而EdgeDB於ext::pg_trgm中找不到Police，所以報錯。

使用default::Police.name的query則可成功執行：

# ✅
with module ext::pg_trgm,
       names:= array_join(array_agg(default::Police.name), " "), 
select word_similar("陳永仁", names);

變數引用

我們可以在with區塊內，使用前面定義的變數，例如：

with police_names:= Police.name,
     police_spy_names:= PoliceSpy.name,
     names:= array_join(array_agg(police_names union police_spy_names), " "), 
     module ext::pg_trgm,
select word_similarity("陳永仨", names);

• names引用了前面定義的police_names 及police_spy_names。

insert此場景的Scene

insert Scene {
      title:= "誰是內鬼", 
      detail:= "毒品交易失敗後，韓琛確信身邊有警察臥底。依照建明要求，" ++
               "將所有小弟的個人資料裝在信封中，於電影院L13位置交給建" ++
               "明。永仁偷偷在後觀察並尾隨建明離開，欲看清其模樣，不料" ++
               "手機突然響起，因而錯失良機。而建明也隱約感覺到有人跟蹤，" ++
               "隱於牆後查看但未發現人跡。另一方面，警隊高層也懷疑韓琛" ++
               "安插了臥底，於是將建明調至內務部並在O記辦公，專門調查此" ++
               "事。建明依照韓琛所給資料於警隊資料庫中進行搜尋，卻無發現" ++
               "。於此同時，韓琛試探了身邊所有小弟，包括永仁與傻強...",
      who:= {hon, chen, lau},
      `when`:= (insert FuzzyTime {
                     fuzzy_year:= 2002,
                     fuzzy_month:= 11,
                     fuzzy_day:= 23,
              }),
      where:= police_station union (insert Location {name:="電影院"}),         
};

無間吹水

黃sir於劇末的墓碑往生日期為2002年11月23日，而其識別證上更換日期卻為2008年7月31日。

參考資料

無間EdgeDB八幕：誰是內鬼。